﻿using System;
using System.Data;
using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client;
using System.Web;
using System.Web.Caching;
using System.Collections.Generic;
using LMD.Constant;
using LMD.Lib.Oracle;
using LMD.Lib.DataClass;
using System.Web.Script.Serialization;

namespace Cached
{
    /// <summary>
    /// Summary description for ContactPersonTable
    /// </summary>
    public class ContactPersonTable : CacheAbstraction
    {
        public DataTable GetContactPersonList(int iCustomerId)
        {
            return GetContactPersonList(iCustomerId, false);
        }
        public DataTable GetContactPersonList(int iCustomerId, bool bForceRefresh)
        {
            String sxCacheKey = KCache.V_CONTACTPERSON + iCustomerId.ToString();

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();

                OracleParameter prmID = new OracleParameter();
                prmID.ParameterName = "prmID";
                prmID.OracleDbType = OracleDbType.Number;
                prmID.Value = iCustomerId;

                String sxQuery =
                    @"
                    SELECT
                        v1.v_customer_contact_id ID,
                        v1.v_customer_contact_id || '_' || v1.IS_NEW_CP ID_COMBINED,
                        v1.name NAME,
                        DECODE(v1.gender, 'L', 'Laki-Laki', 'P', 'Perempuan')  GENDER,
                        v1.EMAIL,
                        v1.PHONE,
                        v1.CELLPHONE,
                        --v1.IS_NEW_CP,            
                        v1.P_PIC_POSITION_CODE POSITION,
                        v1.customer_id
                    FROM 
                        V_ICP_CUSTOMER_CONTACT v1
                    WHERE 
                        customer_id = :prmID
                    ";

                DataTable dt = dbl.GetDataTable(sxQuery, prmID);

                AddToCache(sxCacheKey, dt, CacheType.Daily);

            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        public ContactInfoData GetContactInfoData(int iContactPersonId, bool IsNewCP)
        {
            return GetContactInfoData(iContactPersonId, IsNewCP, false);
        }

        //added by audy to search contact person
        public string searchContactPerson(string searchText, bool bForceRefresh)
        {
            String sxCacheKey = KCache.V_CONTACT_DATA + "CPCacheBox";

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }
            JavaScriptSerializer js = new JavaScriptSerializer();

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();

                OracleParameter prmID = new OracleParameter();
                prmID.ParameterName = "prmID";
                prmID.OracleDbType = OracleDbType.VarChar;
                prmID.Value = searchText;

                String sxQuery =
                    @"
                    SELECT 
                        v1.name NAME,
                        v1.customer_id,
                        DECODE(v1.gender, 'L', 1, 'P', 2)  GENDER,
                        v1.EMAIL,
                        v1.PHONE,
                        v1.CELLPHONE,
                        v1.IS_NEW_CP,
                        v1.IS_CP_TEKNIS,
                        v1.P_PIC_POSITION_ID,
                        v1.P_PIC_POSITION_CODE POSITION,
                        v1.v_customer_contact_id
                    FROM 
                        V_ICP_CUSTOMER_CONTACT v1
                    WHERE 
                        v1.name like '%" + searchText + "%' and rownum <= 10";

                DataTable dt = dbl.GetDataTable(sxQuery);
                List<SearchHelper.ContactPerson> items = new List<SearchHelper.ContactPerson>();
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        SearchHelper.ContactPerson cp = new SearchHelper.ContactPerson();
                        cp.id = dr.ReadInt("customer_id");
                        cp.name = dr["NAME"].ToString();
                        cp.cellphone = dr["CELLPHONE"].ToString();
                        cp.gender = dr["GENDER"].ToString();
                        cp.email = dr["EMAIL"].ToString();
                        items.Add(cp);
                    }
                    AddToCache(sxCacheKey, items, CacheType.Daily);
                }
                else
                {
                    //for (int i = 0; i < 5; i++)
                    //{
                    //    SearchHelper.ContactPerson cp = new SearchHelper.ContactPerson();
                    //    cp.id = i;
                    //    cp.name = "name" + i.ToString();
                    //    cp.cellphone = "cell" + i.ToString();
                    //    cp.gender = "L";
                    //    cp.email = "email" + i.ToString() + "@gmail.com";
                    //    items.Add(cp);
                    //}
                    //AddToCache(sxCacheKey, items, CacheType.Daily);
                }
            }
            return js.Serialize((List<SearchHelper.ContactPerson>)HttpRuntime.Cache[sxCacheKey]);
        }

        public SearchHelper.ContactPerson getContactPerson(int CPID)
        {
           
          
                DBLib dbl = new DBLib();

                OracleParameter prmID = new OracleParameter();
                prmID.ParameterName = "prmID";
                prmID.OracleDbType = OracleDbType.Number;
                prmID.Value = CPID;

                String sxQuery =
                    @"
                    SELECT 
                        v1.name NAME,
                        v1.customer_id,
                        DECODE(v1.gender, 'L', 1, 'P', 2)  GENDER,
                        v1.EMAIL,
                        v1.PHONE,
                        v1.CELLPHONE,
                        v1.IS_NEW_CP,
                        v1.IS_CP_TEKNIS,
                        v1.P_PIC_POSITION_ID,
                        v1.P_PIC_POSITION_CODE POSITION,
                        v1.v_customer_contact_id
                    FROM 
                        V_ICP_CUSTOMER_CONTACT v1
                    WHERE 
                        v1.customer_id=:prmID ";

                DataTable dt = dbl.GetDataTable(sxQuery,prmID);
                SearchHelper.ContactPerson cp = new SearchHelper.ContactPerson();
                if (dt.Rows.Count > 0)
                {   
                    cp.id = dt.Rows[0].ReadInt("customer_id");
                    cp.name = dt.Rows[0]["NAME"].ToString();
                    cp.cellphone = dt.Rows[0]["CELLPHONE"].ToString();
                    cp.gender = dt.Rows[0]["GENDER"].ToString();
                    cp.email = dt.Rows[0]["EMAIL"].ToString();
                }
                else
                {
                    cp.id = 0;
                    //for (int i = 0; i < 5; i++)
                    //{
                    //    SearchHelper.ContactPerson cp = new SearchHelper.ContactPerson();
                    //    cp.id = i;
                    //    cp.name = "name" + i.ToString();
                    //    cp.cellphone = "cell" + i.ToString();
                    //    cp.gender = "L";
                    //    cp.email = "email" + i.ToString() + "@gmail.com";
                    //    items.Add(cp);
                    //}
                    //AddToCache(sxCacheKey, items, CacheType.Daily);
                }


                return cp;
        }


        public ContactInfoData GetContactInfoData(int iContactPersonId, bool IsNewCP, bool bForceRefresh)
        {
            String sxCacheKey = KCache.V_CONTACT_DATA + iContactPersonId.ToString() + IsNewCP.ToString();

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();

                OracleParameter prmID = new OracleParameter();
                prmID.ParameterName = "prmID";
                prmID.OracleDbType = OracleDbType.Number;
                prmID.Value = iContactPersonId;

                OracleParameter prmNewCP = new OracleParameter();
                prmNewCP.ParameterName = "prmNewCP";
                prmNewCP.OracleDbType = OracleDbType.VarChar;
                prmNewCP.Value = IsNewCP ? KGlobal.YES : KGlobal.NO;

                String sxQuery =
                    @"
                    SELECT
                        v1.name NAME,
                        v1.customer_id,
                        DECODE(v1.gender, 'L', 1, 'P', 2)  GENDER,
                        v1.EMAIL,
                        v1.PHONE,
                        v1.CELLPHONE,
                        v1.IS_NEW_CP,
                        v1.IS_CP_TEKNIS,
                        v1.P_PIC_POSITION_ID,
                        v1.P_PIC_POSITION_CODE POSITION
                    FROM 
                        V_ICP_CUSTOMER_CONTACT v1
                    WHERE 
                        v1.v_customer_contact_id = :prmID
                        and v1.IS_NEW_CP = :prmNewCP";

                DataTable dt = dbl.GetDataTable(sxQuery, prmID, prmNewCP);

                ContactInfoData cid = new ContactInfoData();
                cid.ID = iContactPersonId;

                if (dt.Rows.Count <= 0)
                    return cid;

                cid.CompanyID = dt.Rows[0].ReadInt("CUSTOMER_ID");
                cid.JobPositionID = dt.Rows[0].ReadInt("P_PIC_POSITION_ID");
                cid.sxContactName = dt.Rows[0]["NAME"].ToString();
                cid.sxEmailAddress = dt.Rows[0]["EMAIL"].ToString();
                cid.sxJobPositionName = dt.Rows[0]["POSITION"].ToString();
                cid.sxPhoneNumber = dt.Rows[0]["PHONE"].ToString();
                cid.sxCellPhoneNumber = dt.Rows[0]["CELLPHONE"].ToString();
                cid.IsNewCP = IsNewCP;
                cid.IsCPTeknis = dt.Rows[0].ReadString("IS_CP_TEKNIS").ToUpper() == KGlobal.YES;
                cid.Sex = (Gender)dt.Rows[0].ReadInt("GENDER");

                DataTable dtInfos = GetContactInfoList(iContactPersonId, IsNewCP, bForceRefresh);

                foreach (DataRow dr in dtInfos.Rows)
                {
                    String sxKey = Convert.ToString(dr["P_CONTACT_DATA_TYPE_ID"]);
                    String sxValue = Convert.ToString(dr["VALUE"]);

                    cid.AddCustomData(sxKey, sxValue);
                }

                AddToCache(sxCacheKey, cid, CacheType.Daily);

            }
            return (ContactInfoData)HttpRuntime.Cache[sxCacheKey];
        }

        public DataTable GetContactInfoList(int iContactId, bool IsNewCP)
        {
            return GetContactInfoList(iContactId, IsNewCP, false);
        }
        public DataTable GetContactInfoList(int iContactId, bool IsNewCP, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}_{1}", KCache.V_CONTACT_INFO_LIST, iContactId.ToString());

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();

                OracleParameter param = new OracleParameter();
                param.ParameterName = "contactId";
                param.OracleDbType = OracleDbType.Number;
                param.Value = iContactId;

                String sxQuery =
                    IsNewCP ?
                    @"
                    select 
                        t.T_CONTACT_DATA_ID ID, 
                        t.V_CUSTOMER_CONTACT_ID, 
                        t.NEW_CONTACT_PERSON_ID, 
                        t.VALUE, 
                        t.P_CONTACT_DATA_TYPE_ID, 
                        p.CODE INFO_TYPE,
                        p.P_INPUT_CONTROL_TYPE_ID CONTROL_TYPE_ID
                    from 
                        T_CONTACT_DATA t
                        left join P_CONTACT_DATA_TYPE p
                            on t.P_CONTACT_DATA_TYPE_ID = p.P_CONTACT_DATA_TYPE_ID
                    where
                        t.NEW_CONTACT_PERSON_ID = :contactId
                    order by
                        p.CODE ASC" :
                    @"
                    select 
                        t.T_CONTACT_DATA_ID ID, 
                        t.V_CUSTOMER_CONTACT_ID, 
                        t.NEW_CONTACT_PERSON_ID, 
                        t.VALUE, 
                        t.P_CONTACT_DATA_TYPE_ID, 
                        p.CODE INFO_TYPE,
                        p.P_INPUT_CONTROL_TYPE_ID CONTROL_TYPE_ID
                    from 
                        T_CONTACT_DATA t
                        left join P_CONTACT_DATA_TYPE p
                            on t.P_CONTACT_DATA_TYPE_ID = p.P_CONTACT_DATA_TYPE_ID
                    where
                        t.V_CUSTOMER_CONTACT_ID = :contactId
                    order by
                        p.CODE ASC";

                DataTable dt = dbl.GetDataTable(sxQuery, param);

                AddToCache(sxCacheKey, dt, CacheType.Daily);

            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        public override String GetCacheFilename()
        {
            return KCache.Filename.CONTACT_RELATED;
        }
    }
}